# !/usr/bin/env python
# -*- encoding: utf-8 -*-
# @Author : 大野

from pymysql.converters import escape_string



class ServiceVersion:

    def add_case(self, case):
        '''
        创建单条用例
        :param case:
        :return:
        '''
        case_request_data = escape_string(case["case_request_data"])
        case_check = escape_string(case["case_check"])
        case_relation_param = escape_string(case["case_relation_param"])
        template_relation_param = escape_string(case["template_relation_param"])
        template_param = escape_string(case["template_param"])
        template_check = escape_string(case["template_check"])
        list = []
        sql = f'''
             INSERT INTO project_case(`id`, `project_id`, `service_id`, `version_id`,`case_tag`, `case_id`,`case_name`,
           `case_path`, `case_url`, `case_method`,`case_format`, `case_request_data`, `case_check`, `case_relation_param`,
           `case_on_off`,`case_status`,`template_name`,`template_param`,`template_check`,
           `template_relation_param`, `created`, `modified`) VALUES (
           NULL, {case["project_id"]}, 
           {case["service_id"]}, {case["version_id"]},"数云tester", "{case["case_id"]}", "{case["case_name"]}", "{case["case_path"]}", "{case["case_url"]}", "{case["case_method"]}",
           "{case["case_format"]}","{case_request_data}", "{case_check}", "{case_relation_param}", "YES", 0 ,
           "{case["template_name"]}","{template_param}","{template_check}","{template_relation_param}", NOW(),NOW()); 
           '''
        list.append(sql)
        return list

    def add_import_case(self, base_info, version_id, case_list):
        '''
        增加用例
        :param base_info: 基础项目字段
        :param case_list: 用例集
        :return:
        '''
        list = []
        for case in case_list:
            case_url = escape_string(case["case_url"])
            case_request_data = escape_string(case["case_request_data"])
            case_check = escape_string(case["case_check"])
            case_relation_param = escape_string(case["case_relation_param"])
            template_relation_param = escape_string(case["template_relation_param"])
            template_param = escape_string(case["template_param"])
            template_check = escape_string(case["template_check"])
            sql = f'''
                  INSERT INTO project_case(`id`, `project_id`, `service_id`, `version_id`,`case_tag`, 
                `case_id`,`case_name`,
                `case_path`, `case_url`, `case_method`, `case_format`,`case_request_data`, `case_check`, `case_relation_param`,
                `case_on_off`,`case_status`,`template_name`,`template_param`,`template_check`,
                `template_relation_param`, `created`, `modified`) VALUES (
                NULL, {base_info["project_id"]}, 
                {base_info["service_id"]}, {version_id},"{case["case_tag"]}", "{case["case_id"]}", "{case["case_name"]}", "{case["case_path"]}", "{case_url}", "{case["case_method"]}",
                "{case["case_format"]}","{case_request_data}", "{case_check}", "{case_relation_param}", "YES", 0 ,
                "{case["template_name"]}","{template_param}","{template_check}","{template_relation_param}", NOW(),NOW()); '''
            list.append(sql)
        return list

    # 更新用例状态
    def update_case_status(self, id, case_status):
        list = []
        sql = f"UPDATE `project_case` SET  case_status={case_status},  `modified` = NOW() WHERE id = {id};"
        list.append(sql)
        return list

    # 修改用例信息
    def update_case_info(self, data):
        id = data["id"]
        if data["caseall"]["case_on_off"]:
            data["caseall"]["case_on_off"] = 'YES'
        else:
            data["caseall"]["case_on_off"] = 'NO'
        case_on_off = escape_string(data["caseall"]["case_on_off"])
        case_request_data = escape_string(data["caseall"]["case_request_data"])
        case_check = escape_string(data["caseall"]["case_check"])
        case_relation_param = escape_string(data["caseall"]["case_relation_param"])
        template_relation_param = escape_string(data["caseall"]["template_relation_param"])
        template_param = escape_string(data["caseall"]["template_param"])
        template_check = escape_string(data["caseall"]["template_check"])
        list = []
        sql = f'''
             UPDATE project_case set `case_id`="{data["caseall"]["case_id"]}",`case_name`="{data["caseall"]["case_name"]}",
           `case_path`= "{data["caseall"]["case_path"]}", `case_url`=  "{data["caseall"]["case_url"]}", `case_method`= "{data["caseall"]["case_method"]}",
           `case_format` = "{data["caseall"]["case_format"]}",
            `case_request_data`= "{case_request_data}", `case_check`= "{case_check}", `case_relation_param`= "{case_relation_param}",
           `case_on_off`= "{case_on_off}",`case_status`= 0 ,`template_name`= "{data["caseall"]["template_name"]}", 
           `template_param` = "{template_param}", 
            `template_check`= "{template_check}",
           `template_relation_param`= "{template_relation_param}", `modified`=NOW() where id = {id}; 
           '''
        list.append(sql)
        return list

    # 删除用例
    def delete_case(self, id):
        list1 = []
        if isinstance(id, list):
            sql = f"delete from project_case where id in {tuple(id)};"
            list1.append(sql)
            return list1
        else:
            sql = f"delete from project_case where id = {id};"
            list1.append(sql)
            return list1

    def add_case_log_id(self, data, logname, source):
        '''增加执行用例记录'''
        list1 = []
        sql = f''' INSERT INTO case_log_id( `project_id`, `service_id`, `version_id`, `log_name`, `username`,`source`, `created`) 
        VALUES ({data["project_id"]}, {data["service_id"]}, "{data["version_name"]}", '{logname}', '{data["username"]}',{source}, NOW());
                        '''
        list1.append(sql)
        return list1

    def add_task_log_id(self, data, logname, source):
        list1 = []
        sql = f''' INSERT INTO `task_log_id`(`name`, `task_name`, `task_service`, `task_version`, `created`) 
        VALUES ('{logname}', '{data["task_name"]}', 'None', 'None', NOW());
        '''
        list1.append(sql)
        return list1

    def select_case_log_id(self, name):
        '''
        根据名称查询
        :param version_id:
        :return:
        '''
        list1 = []
        sql = f''' select id,log_name,username,created from case_log_id where log_name='{name}';'''
        list1.append(sql)
        return list1

    def select_case_version_id(self, data):
        '''
        根据版本号查询
        :param version_id:
        :return:
        '''
        list1 = []
        sql = f''' select id,log_name,username,created from case_log_id where project_id={data['project_id']} and service_id={data['service_id']} and version_id="{data['version_name']}" ORDER BY created DESC ;'''
        list1.append(sql)
        return list1

    def select_case_service(self, service_id):
        '''
        根据根据服务查询所有版本记录
        :param version_id:
        :return:
        '''
        list1 = []
        sql = f'''select id,log_name,version_id
                    from case_log_id 
                    where service_id="{service_id}" GROUP BY version_id; '''
        list1.append(sql)
        return list1

    def add_case_response_log(self, results, log_id):
        '''
        插入响应结果
        :param data: 前端源数据
        :param result1: 执行结果
        :return: sql
        '''
        list1 = []
        if isinstance(results, list):
            for result in results:
                resp = escape_string(result['response'])
                check = escape_string(result["case_check"])
                if result["dbresponse"]:
                    sql = f''' INSERT INTO `case_response_log`( `project_id`, `service_id`, `version_id`, `case_id`, 
                    `case_name`, `case_check`, `response`, `responseTime`, `success`,`dbsuccess`, `created`, 
                    `case_log_id`,`project_case_id`, `dbresponse`) VALUES ( {result["project_id"]}, 
                    {result["service_id"]}, "{result["version_id"]}", "{result["case_id"]}", "{result["case_name"]}", "{check}", 
                    "{resp}", "{result["responseTime"]}", "{result["success"]}","{result["dbsuccess"]}",NOW(),{log_id},{result["id"]},"{result["dbresponse"]}"); 
                            '''
                    list1.append(sql)
                else:
                    sql = f''' INSERT INTO `case_response_log`( `project_id`, `service_id`, `version_id`, `case_id`, 
                    `case_name`, `case_check`, `response`, `responseTime`, `success`,`dbsuccess`, `created`, 
                    `case_log_id`,`project_case_id`, `dbresponse`) VALUES ( {result["project_id"]}, 
                                        {result["service_id"]}, "{result["version_id"]}", "{result["case_id"]}", "{result["case_name"]}", "{check}", 
                                        "{resp}", "{result["responseTime"]}", "{result["success"]}","None",NOW(),{log_id},{result["id"]},"None"); 
                                                '''
                    list1.append(sql)
        else:
            return 'error'
        return list1

    def select_case_res_log(self, id):
        list1 = []
        sql = f'select * from case_response_log crl INNER JOIN project_case pc ON crl.project_case_id=pc.id where crl.case_log_id = {id} '
        list1.append(sql)
        return list1

    # 查询某个版本的用例
    def select_case_(self, data):
        list1 = []
        if isinstance(data["version_name"], list) and len(data["version_name"]) > 1:
            sql = f"select * from project_case where project_id ={data['project_id']} and service_id={data['service_id']} and version_id in (select id from project_version where service_id={data['service_id']} and version_name in {tuple(data['version_name'])})"
            list1.append(sql)
        elif isinstance(data["version_name"], list) and len(data["version_name"]) == 1:
            sql = f"select * from project_case where project_id ={data['project_id']} and service_id={data['service_id']} and version_id = (select id from project_version where service_id={data['service_id']} and version_name = '{data['version_name'][0]}')"
            list1.append(sql)
        else:
            sql = f"select * from project_case where project_id ={data['project_id']} and service_id={data['service_id']} and version_id = (select id from project_version where service_id={data['service_id']} and version_name = '{data['version_name']}')"
            list1.append(sql)
        return list1

    # 根据ID查询用例
    def select_case_id(self, id):
        list1 = []
        sql = f"select * from project_case where id ={id} ORDER BY created DESC"
        list1.append(sql)
        return list1

    def select_project_path_url(self, case_path_name):
        '''
        查询用例url映射关系
        :param case_path_name:
        :return:
        '''
        list = []
        sql = f"select case_path_url from project_url_mapping where case_path_name = '{case_path_name}';"
        list.append(sql)
        return list

# if __name__ == '__main__':
#     a =  {'project_id': '34', 'service_id': '7', 'version_id': '43'}
#     b = [{'case_tag': '测试用例', 'case_id': 'cb_3', 'case_name': '模板创建-ual请求', 'case_path': 'contentUrl-ual', 'case_url': 'contentmanage/v1/content/save', 'case_method': 'POST', 'case_request_data': '{"cid":null,"componentSet":"image,text","components":[{"label":"图片","name":"image","isNew":false,"authority":true,"disMove":true,"id":"image_0","labelLen":0,"style":{"ml":8,"mr":8,"mt":10,"mc":8},"type":"list","values":[{"eleId":"image_0_0","landingPage":"","img":"http://content-management.xxxxxx.com/1565764401003.gif","labels":[],"name":"","url":""}],"question_finish_abled":true,"is_independent":false},{"label":"文本","name":"text","isNew":false,"authority":true,"disMove":true,"id":"text_0","style":{"ml":8,"mr":8,"mt":10,"mc":8},"values":"<div>自动化测试</div>\\n","question_finish_abled":true,"is_independent":false}],"cover":"","description":"","groupId":"","isCoBrand":0,"isIndiv":0,"status":0,"subTitle":"自动化测试","thumbnail":"","title":"自动化测试","type":"mobile","userId":"taobao_100008795","userName":"梵希蔓希蔓专卖店","questionEndDate":"","isShow":0,"isMusic":0,"isQuestionNaire":0}', 'case_check': '{"code":1,"msg":"保存成功"}', 'case_relation_param': "${cid}=['cid']", 'template_name': '', 'template_param': '', 'template_check': '', 'template_relation_param': ''}, {'case_tag': '测试用例', 'case_id': 'cb_4', 'case_name': '模板发布1-ual请求', 'case_path': 'contentUrl-ual', 'case_url': 'contentmanage/v1/content/publish/${cid | cb_3}?componentSet="image,text"', 'case_method': 'GET', 'case_request_data': '{}', 'case_check': '{"msg":"发布成功!"}', 'case_relation_param': "${random}=['random']", 'template_name': '', 'template_param': '', 'template_check': '', 'template_relation_param': ''}, {'case_tag': '测试用例', 'case_id': 'cb_5', 'case_name': '模板发布2-ual请求', 'case_path': 'contentUrl-ual', 'case_url': 'contentmanage/v1/content/publish/${cid | cb_3}', 'case_method': 'GET', 'case_request_data': '{"componentSet":"share"}', 'case_check': '{"msg":"发布成功!"}', 'case_relation_param': '', 'template_name': '', 'template_param': '', 'template_check': '', 'template_relation_param': ''}, {'case_tag': '测试用例', 'case_id': 'cb_1', 'case_name': '查询模板-原请求', 'case_path': 'contentUrl', 'case_url': 'serve/h5Model/', 'case_method': 'POST', 'case_request_data': '{\n\t"shop_id":"taobao_100008795",\n\t"page_index":1,\n\t"page_size":100,\n\t"thumbnail":1,\n\t"keyword":"自动化测试"\n}', 'case_check': '{\n    "data": [\n        {                  "cid": ${cid|cb_3}}\n    ]}', 'case_relation_param': '', 'template_name': '', 'template_param': '', 'template_check': '', 'template_relation_param': ''}, {'case_tag': '测试用例', 'case_id': 'cb_2', 'case_name': '查询模板-ual请求', 'case_path': 'contentUrl-ual', 'case_url': 'contentmanage/v1/serve/h5Model/', 'case_method': 'POST', 'case_request_data': '{\n\t"shop_id":"taobao_100008795",\n\t"page_index":1,\n\t"page_size":100,\n\t"thumbnail":1,\n\t"keyword":"自动化测试"\n}', 'case_check': '{\n    "data": [\n        {                  "cid": ${cid}|cb_3}}\n    ]}', 'case_relation_param': '', 'template_name': '', 'template_param': '', 'template_check': '', 'template_relation_param': ''}, {'case_tag': '测试用例', 'case_id': 'cb_6', 'case_name': '发送事件关怀短链-ual请求', 'case_path': 'contentUrl-ual', 'case_url': 'contentmanage/v1/contentEvent/getShortLink', 'case_method': 'POST', 'case_request_data': '{\n  "tid": "1000006010000483",\n  "logisticsNo": 100001,\n  "tenantId": "qiushi6",\n  "templateUrl": "http://pre.vcrm.me/${random|cb_4}",\n  "buyerNick":["ltz166"],\n  "plat": "taobao",\n  "shopId": "100008795",\n  "type": "1",\n  "appId": "0",\n  "shopName":"梵希蔓希蔓专卖店",\n  "mobile":"18616029793",\n  "cid":${cid|cb_3}\n}', 'case_check': '{\n    "msg": "获取短链成功"}', 'case_relation_param': "${shorturl}=['data']", 'template_name': '', 'template_param': '', 'template_check': '', 'template_relation_param': ''}, {'case_tag': '测试用例', 'case_id': 'cb_7', 'case_name': '发送事件关怀短链-原请求', 'case_path': 'contentUrl', 'case_url': 'contentEvent/getShortLink', 'case_method': 'POST', 'case_request_data': '{\n  "tid": "1000006010000483",\n  "logisticsNo": 100001,\n  "tenantId": "qiushi6",\n  "templateUrl": "http://pre.vcrm.me/${random|cb_4}",\n  "buyerNick":["ltz166"],\n  "plat": "taobao",\n  "shopId": "100008795",\n  "type": "1",\n  "appId": "0",\n  "shopName":"梵希蔓希蔓专卖店",\n  "mobile":"18616029793",\n  "cid":${cid|cb_3}\n}', 'case_check': '{"msg": "获取短链成功"}', 'case_relation_param': '', 'template_name': '', 'template_param': '', 'template_check': '', 'template_relation_param': ''}, {'case_tag': '测试用例', 'case_id': 'cb_8', 'case_name': '物流短链-ual请求', 'case_path': 'contentUrl-ual', 'case_url': 'contentmanage/v1/contentEvent/getLogisticsH5', 'case_method': 'POST', 'case_request_data': '{\n    "status": 1,\n    "type": "mobile",\n    "isCoBrand": 0,\n    "cid": ${cid|cb_3},\n    "subTitle": "自动化测试_001",\n    "title": "自动化测试",\n    "isIndiv": 0,\n    "userId": "taobao_100008795",\n    "userName": "梵希蔓希蔓专卖店",\n    "optId":"10000316",\n    "optName":"limengjian"\n}', 'case_check': '{\n    "msg": "获取物流组件H5成功"}', 'case_relation_param': "${cid2}=['data']['cid']", 'template_name': '', 'template_param': '', 'template_check': '', 'template_relation_param': ''}, {'case_tag': '测试用例', 'case_id': 'cb_9', 'case_name': '物流短链-原请求', 'case_path': 'contentUrl', 'case_url': 'contentEvent/getLogisticsH5', 'case_method': 'POST', 'case_request_data': '{\n    "status": 1,\n    "type": "mobile",\n    "isCoBrand": 0,\n    "cid": ${cid|cb_3},\n    "subTitle": "自动化测试_001",\n    "title": "自动化测试",\n    "isIndiv": 0,\n    "userId": "taobao_100008795",\n    "userName": "梵希蔓希蔓专卖店",\n    "optId":"10000316",\n    "optName":"limengjian"\n}', 'case_check': '{\n    "msg": "获取物流组件H5成功"}', 'case_relation_param': '', 'template_name': '', 'template_param': '', 'template_check': '', 'template_relation_param': ''}, {'case_tag': '测试用例', 'case_id': 'cb_10', 'case_name': '主动营销动态短链-原请求', 'case_path': 'contentUrl', 'case_url': 'content/getShortLink', 'case_method': 'POST', 'case_request_data': '{\n  "task_id": "autotest_60_10000483",\n  "send_count": 10,\n  "tenant_id": "qiushi6",\n  "template_url": "http://pre.vcrm.me/${random|cb_4}",\n  "buyer_nick":["ltz166"],\n  "plat": "taobao",\n  "shop_id": "100008795",\n  "type": "1",\n  "app_id": "0"\n}', 'case_check': '200', 'case_relation_param': "${shorturl2}=['ltz166']", 'template_name': '', 'template_param': '', 'template_check': '', 'template_relation_param': ''}, {'case_tag': '测试用例', 'case_id': 'cb_11', 'case_name': '主动营销动态短链-ual请求', 'case_path': 'contentUrl-ual', 'case_url': 'contentmanage/v1/content/getShortLink', 'case_method': 'POST', 'case_request_data': '{\n  "task_id": "autotest_60_10000483",\n  "send_count": 10,\n  "tenant_id": "qiushi6",\n  "template_url": "http://pre.vcrm.me/${random|cb_4}",\n  "buyer_nick":["ltz166"],\n  "plat": "taobao",\n  "shop_id": "100008795",\n  "type": "1",\n  "app_id": "0"\n}', 'case_check': '200', 'case_relation_param': '', 'template_name': '', 'template_param': '', 'template_check': '', 'template_relation_param': ''}, {'case_tag': '测试用例', 'case_id': 'cb_12', 'case_name': '动态短链校验1', 'case_path': 'contentUrl-ual', 'case_url': 'contentmanage/v1/content/getUrlToContentNew', 'case_method': 'GET', 'case_request_data': 'code=&scene=nwx&url=${shorturl|cb_6}', 'case_check': '{\n    "data": {\n        "cid": 41787\n    }\n}', 'case_relation_param': '', 'template_name': '', 'template_param': '', 'template_check': '', 'template_relation_param': ''}, {'case_tag': '测试用例', 'case_id': 'cb_13', 'case_name': '动态短链校验2', 'case_path': 'contentUrl-ual', 'case_url': 'contentmanage/v1/content/getUrlToContentNew', 'case_method': 'GET', 'case_request_data': 'code=&scene=nwx&url=${shorturl2|cb_10}', 'case_check': '{\n    "data": {\n        "cid": 41787\n    }\n}', 'case_relation_param': '', 'template_name': '', 'template_param': '', 'template_check': '', 'template_relation_param': ''}, {'case_tag': '测试用例', 'case_id': 'cb_14', 'case_name': '删除模板1', 'case_path': 'contentUrl-ual', 'case_url': 'contentmanage/v1/content/delete/taobao_100008795/${cid2|cb_8}', 'case_method': 'DELETE', 'case_request_data': '', 'case_check': '{\n    "msg": "操作成功！",\n    "code": 1\n}', 'case_relation_param': '', 'template_name': '', 'template_param': '', 'template_check': '', 'template_relation_param': ''}, {'case_tag': '测试用例', 'case_id': 'cb_15', 'case_name': '删除模板2', 'case_path': 'contentUrl-ual', 'case_url': 'contentmanage/v1/content/delete/taobao_100008795/${cid|cb_3}', 'case_method': 'DELETE', 'case_request_data': '', 'case_check': '{\n    "msg": "操作成功！",\n    "code": 1\n}', 'case_relation_param': '', 'template_name': '', 'template_param': '', 'template_check': '', 'template_relation_param': ''}]
#
#
#     print(ServiceVersion().add_import_case(a,b))
